package com.testAll;

import com.core.comment.StringUtil;
import com.core.jdbc.SqlHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class poiTest3 {
    public static void main(String[] args) throws SQLException {
        testSql();
    }

    public static void testSql() throws SQLException {
        String sql = "SELECT kecheng,COUNT(1) from xxxjiaoan2 GROUP BY kecheng HAVING COUNT(1)=17;";
        System.out.println(sql);
        List<Map<String, Object>> list = SqlHelper.queryMapList(sql, new HashMap<>());
        String r = "";
        for (Map<String, Object> map : list) {
            if (StringUtil.isBlack(r)) {
                r = r + "'"+map.get("kecheng").toString()+"'";
            } else {
                r = r + "," + "'"+map.get("kecheng").toString()+"'";
            }

        }
        System.out.println(r);
        //SqlHelper.sqlOperateList(list);
    }

    public static List<String> parseExcel(String path) throws SQLException {
        List<String> list = new ArrayList<>();
        try {
            //1、获取文件输入流
            InputStream inputStream = new FileInputStream(path);
            //2、获取Excel工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

            //3、得到Excel工作表对象
            XSSFSheet sheetAt2 = workbook.getSheetAt(1);
            String sheetName = sheetAt2.getSheetName();
            //4、循环读取表格数据
            int start = 0;
            for (Row row : sheetAt2) {
                //首行（即表头）不读取
                if (row.getRowNum() <= 1) {
                    continue;
                }
                ++start;
                //读取当前行中单元格数据，索引从0开始
                String huanjie = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : null;
                String huanjie2 = row.getCell(1) != null ? row.getCell(1).getStringCellValue() : null;
                String shijian = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : null;
                String mubiao = row.getCell(3) != null ? row.getCell(3).getStringCellValue() : null;
                String jiaoju = row.getCell(4) != null ? row.getCell(4).getStringCellValue() : null;
                String fangfa = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : null;

                //System.out.println(huanjie + "  "+huanjie2+" " + shijian + " " + mubiao + " " + jiaoju + " " + fangfa);

                String sql = " INSERT INTO xxxjiaoan2 VALUES ('" + start + "', '" + sheetName + "', '" + huanjie + "', '" + huanjie2 + "', '" + shijian + "', '" + mubiao + "', '" + jiaoju + "', '" + fangfa + "', null); ";
                System.out.println(sql);
                list.add(sql);
            }

            //5、关闭流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //SqlHelper.sqlOperateList(list);
        return list;
    }
}
